/**
 * 
 */
package org.vsg.stock.basic.persistence.hibernate;

import java.sql.SQLException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Vector;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.dao.DataAccessException;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.HibernateTemplate;
import org.vsg.common.orm.hibernate.AbstractGenericDaoImpl;
import org.vsg.common.pagination.ext.ExtensionFunction;
import org.vsg.common.pagination.processor.hibernate.HibernatePaginationQuery;
import org.vsg.stock.basic.StockHelper;
import org.vsg.stock.basic.persistence.StockDailyLineDao;
import org.vsg.stock.core.domain.StockDailyKLine;

/**
 * @author vison
 *
 */

public class StockDailyLineDaoImpl extends AbstractGenericDaoImpl implements
		StockDailyLineDao,InitializingBean {

	private DecimalFormat decFormat = new DecimalFormat("##0.00000");
	

	
	/* (non-Javadoc)
	 * @see org.vsg.stock.basic.persistence.StockDailyLineDao#saveAll(java.util.Collection)
	 */
	@Override
	public void saveAll(Collection<StockDailyKLine> entities)
			throws DataAccessException {
		// TODO Auto-generated method stub
		this.getTemplate().saveOrUpdateAll(entities);
		logger.debug("update and save entity " );
	}

	@Override
	public List<StockDailyKLine> findStockLineByCycle(String code,
			Date startDate, int cycle) throws DataAccessException {
		// TODO Auto-generated method stub
		Date toDate = null;
		Calendar tmpCale = Calendar.getInstance();
		tmpCale.setTime(startDate);
		
		StockHelper inst = StockHelper.getInstance();
		
		// --- fix the long holiday
		for (int i = 0 ; i < cycle + 60 ; i++) {
			tmpCale.add(Calendar.DATE, -1);
			if (!inst.isTradingDate(tmpCale)) {
				continue;
			}
		}
		toDate = tmpCale.getTime();

		
		
		StringBuilder query = new StringBuilder("SELECT ");
		query.append("stockDailyLine ");
		query.append("from ").append(StockDailyKLine.class.getName());
		query.append(" as stockDailyLine ");
		query.append("WHERE stockDailyLine.stockCode = ? AND stockDailyLine.date <= ? AND stockDailyLine.date > ? ");
		query.append("ORDER BY stockDailyLine.date DESC");
		
		Collection paramColl = new Vector();
		paramColl.add( code );
		paramColl.add( startDate );
		paramColl.add( toDate );
		
		if (logger.isDebugEnabled()) {
			logger.debug("Execute SQL :"+query.toString());
		}

		HibernatePaginationQuery hibernateQuery = new HibernatePaginationQuery();
		hibernateQuery.setQuery(query.toString());
		hibernateQuery.setParams(paramColl.toArray());
		hibernateQuery.setStartIndex(0);
		hibernateQuery.setRows(cycle);
		
		HibernateTemplate template = this.getTemplate();
		
		List<StockDailyKLine> dataList = new ArrayList<StockDailyKLine>();

		List objList = (List)template.executeFind(hibernateQuery);
		Iterator dataIter = objList.iterator();			
		StockDailyKLine entity = null;
		while (dataIter.hasNext()) {
			entity = (StockDailyKLine)dataIter.next();
			dataList.add(entity);				
		}
		
		return dataList;

	}

	
	@Override
	public List<StockDailyKLine> findStockLineByOverRatio(Date refDate,
			double ratio) throws DataAccessException {
		// TODO Auto-generated method stub
		Date toDate = null;
		Calendar tmpCale = Calendar.getInstance();
		tmpCale.setTime(refDate);
		// --- fix the long holiday
		tmpCale.add(Calendar.DATE, -1);
		toDate = tmpCale.getTime();
		
		StringBuilder query = new StringBuilder("SELECT ");
		query.append("stockDailyLine ");
		query.append("from ").append(StockDailyKLine.class.getName());
		query.append(" as stockDailyLine ");
		query.append("WHERE stockDailyLine.date <= ? AND stockDailyLine.date > ? ");
		query.append("AND stockDailyLine.yieldRate >= ?  ");
		query.append("ORDER BY stockDailyLine.yieldRate DESC");
		
		Collection paramColl = new Vector();
		paramColl.add( refDate );
		paramColl.add( toDate );
		paramColl.add( Double.parseDouble( decFormat.format( ratio / 100 ) )  );
		
		if (logger.isDebugEnabled()) {
			logger.debug("Execute SQL :"+query.toString());
		}

		HibernateTemplate template = this.getTemplate();
		
		List<StockDailyKLine> dataList = new ArrayList<StockDailyKLine>();

		Iterator dataIter = template.iterate(query.toString() ,paramColl.toArray() );		
		StockDailyKLine entity = null;
		while (dataIter.hasNext()) {
			entity = (StockDailyKLine)dataIter.next();
			dataList.add(entity);				
		}
		
		return dataList;
	}

	@Override
	public long findMinVolInCycle(String stockCode, Date fromDate, int cycle)
			throws DataAccessException {
		// TODO Auto-generated method stub
		Date toDate = null;
		Calendar tmpCale = Calendar.getInstance();
		tmpCale.setTime(fromDate);

		StockHelper inst = StockHelper.getInstance();		
		
		// --- fix the long holiday
		while (cycle > 0) {

			if (!inst.isTradingDate( tmpCale ) ) {
				tmpCale.add(Calendar.DATE, -1);	
				continue;
			}			
			
			tmpCale.add(Calendar.DATE, -1);					
			--cycle;
		}		
		toDate = tmpCale.getTime();
		
		
		StringBuilder query = new StringBuilder("SELECT ");
		query.append("min(stockDailyLine.vol) ");
		query.append("from ").append(StockDailyKLine.class.getName());
		query.append(" as stockDailyLine ");
		query.append("WHERE stockDailyLine.stockCode = ?  ");
		query.append("AND stockDailyLine.date < ? AND stockDailyLine.date > ? ");
		
		Collection paramColl = new Vector();
		paramColl.add( stockCode.toUpperCase() );
		paramColl.add( fromDate );
		paramColl.add( toDate );
		
		if (logger.isDebugEnabled()) {
			logger.debug("Execute SQL :"+query.toString());
		}		
		
		HibernateTemplate template = this.getTemplate();

		Iterator<Long> dataIter = template.iterate(query.toString() ,paramColl.toArray() );		
		long result = 0;
		if (dataIter.hasNext() ) {
			Long r = dataIter.next();
			if (r != null) {
				result = r;
				
			}
		}
		return result;
	}

	@Override
	public StockDailyKLine getStkDailyLineByMinVolInCycle(String stockCode,
			long vol, Date fromDate, int cycle) throws DataAccessException {
		// TODO Auto-generated method stub
		Date toDate = null;
		Calendar tmpCale = Calendar.getInstance();
		tmpCale.setTime(fromDate);

		StockHelper inst = StockHelper.getInstance();		
		
		// --- fix the long holiday
		while (cycle > 0) {

			if (!inst.isTradingDate( tmpCale ) ) {
				tmpCale.add(Calendar.DATE, -1);	
				continue;
			}			
			
			tmpCale.add(Calendar.DATE, -1);					
			--cycle;
		}		
		toDate = tmpCale.getTime();
		
		
		StringBuilder query = new StringBuilder("SELECT ");
		query.append("stockDailyLine ");
		query.append("from ").append(StockDailyKLine.class.getName());
		query.append(" as stockDailyLine ");
		query.append("WHERE stockDailyLine.stockCode = ?  ");
		query.append("AND stockDailyLine.date < ? AND stockDailyLine.date > ? ");
		query.append("AND stockDailyLine.vol = ? ");
		
		Collection paramColl = new Vector();
		paramColl.add( stockCode.toUpperCase() );
		paramColl.add( fromDate );
		paramColl.add( toDate );
		paramColl.add( vol );
		
		if (logger.isDebugEnabled()) {
			logger.debug("Execute SQL :"+query.toString());
		}		
		
		HibernateTemplate template = this.getTemplate();
		Iterator dataIter = template.iterate(query.toString() ,paramColl.toArray() );		
		StockDailyKLine entity = null;
		if (dataIter.hasNext()) {
			entity = (StockDailyKLine)dataIter.next();
		}		
		return entity;
	}

	
	@Override
	public StockDailyKLine getStkDailyLineByMinVolInCycle(String stockCode,
			Date fromDate, int cycle) throws DataAccessException {
		// TODO Auto-generated method stub
		Date toDate = null;
		Calendar tmpCale = Calendar.getInstance();
		tmpCale.setTime(fromDate);
		
		StockHelper inst = StockHelper.getInstance();		
		
		// --- fix the long holiday
		while (cycle > 0) {

			if (!inst.isTradingDate( tmpCale ) ) {
				tmpCale.add(Calendar.DATE, -1);	
				continue;
			}			
			
			tmpCale.add(Calendar.DATE, -1);					
			--cycle;
		}		
		toDate = tmpCale.getTime();
		
		StringBuilder subQuery = new StringBuilder("SELECT ");
		subQuery.append("min(stockDailyLine.vol) ");
		subQuery.append("from ").append(StockDailyKLine.class.getName());
		subQuery.append(" as stockDailyLine ");
		subQuery.append("WHERE stockDailyLine.stockCode = ?  ");
		subQuery.append("AND stockDailyLine.date < ? AND stockDailyLine.date > ? ");
		
		StringBuilder query = new StringBuilder("SELECT ");
		query.append("stockDailyLine ");
		query.append("from ").append(StockDailyKLine.class.getName());
		query.append(" as stockDailyLine ");
		query.append("WHERE stockDailyLine.stockCode = ?  ");
		query.append("AND stockDailyLine.date < ? AND stockDailyLine.date > ? ");
		query.append("AND stockDailyLine.vol = (");
		query.append(subQuery);
		query.append(")");
		
		Collection paramColl = new Vector();
		paramColl.add( stockCode.toUpperCase() );
		paramColl.add( fromDate );
		paramColl.add( toDate );
		paramColl.add( stockCode.toUpperCase() );
		paramColl.add( fromDate );
		paramColl.add( toDate );
		
		if (logger.isDebugEnabled()) {
			logger.debug("Execute SQL :"+query.toString());
		}		
		
		HibernateTemplate template = this.getTemplate();
		Iterator dataIter = template.iterate(query.toString() ,paramColl.toArray() );		
		StockDailyKLine entity = null;
		if (dataIter.hasNext()) {
			entity = (StockDailyKLine)dataIter.next();
		}		
		return entity;
	}

	
	
	
	
	@Override
	public List<StockDailyKLine> findStockLineByAll(String code)
			throws DataAccessException {
		// TODO Auto-generated method stub

		
		StringBuilder query = new StringBuilder("SELECT ");
		query.append("StockDailyKLine ");
		query.append("from ").append(StockDailyKLine.class.getName());
		query.append(" as StockDailyKLine ");
		query.append("WHERE StockDailyKLine.stockCode = ? ");
		query.append("ORDER BY StockDailyKLine.date DESC");
		
		Collection paramColl = new Vector();
		paramColl.add( code );
		
		if (logger.isDebugEnabled()) {
			logger.debug("Execute SQL :"+query.toString());
		}

		HibernateTemplate template = this.getTemplate();
		
		List<StockDailyKLine> dataList = new ArrayList<StockDailyKLine>();

		Iterator dataIter = template.iterate(query.toString() ,paramColl.toArray() );		
		StockDailyKLine entity = null;
		while (dataIter.hasNext()) {
			entity = (StockDailyKLine)dataIter.next();
			dataList.add(entity);				
		}
		
		return dataList;
	}

	
	@Override
	public StockDailyKLine getDailyLineFromDate(String stockCode, Date date)
			throws DataAccessException {

		StringBuilder query = new StringBuilder("SELECT ");
		query.append("StockDailyKLine ");
		query.append("from ").append(StockDailyKLine.class.getName());
		query.append(" as StockDailyKLine ");
		query.append("WHERE StockDailyKLine.stockCode = ? ");
		query.append("AND StockDailyKLine.date >= ? ");		
		query.append("AND StockDailyKLine.date <= ? ");		
		
		
		Collection paramColl = new Vector();
		paramColl.add( stockCode );
		paramColl.add( ExtensionFunction.changeFirstTimeOfDate(date) );
		paramColl.add( ExtensionFunction.changeLastTimeOfDate(date) );
		
		if (logger.isDebugEnabled()) {
			logger.debug("Execute SQL :"+query.toString());
		}

		HibernateTemplate template = this.getTemplate();
		

		Iterator dataIter = template.iterate(query.toString() ,paramColl.toArray() );		
		StockDailyKLine entity = null;
		if (dataIter.hasNext()) {
			entity = (StockDailyKLine)dataIter.next();
		
		}
		
		return entity;
	}

	@Override
	public void updateStockDateExRights(final String stkCode, final Date refDate)
			throws DataAccessException {
		// TODO Auto-generated method stub
		final StringBuilder qStr = new StringBuilder("update ");
		qStr.append("STOCK_DAILY_LINE as mas_line");
		qStr.append("left outer join ").append("STOCK_DAILY_LINE as sub_line ");
		qStr.append("on mas_line.stock_code = sub_line.stock_code ");
		qStr.append("and ");
		qStr.append("(")
			.append("mas_line.stock_date = adddate(sub_line.stock_date,1) ")
			.append("or (dayOfweek(mas_line.stock_date) = 2 and mas_line.stock_date = adddate(sub_line.stock_date,3)) ");
		qStr.append("set mas_line.ex_rights = ")
			.append("abs(mas_line.yield_rate * 100)>=11 && abs(round( (mas_line.open - sub_line.close)/sub_line.close * 100 , 3)) >= 11 ");
		qStr.append("where mas_line.stock_code = ? and mas_line.stock_date = ? and sub_line.id is not null ");
		
		
		HibernateCallback action = new HibernateCallback() {
			@Override
			public Object doInHibernate(Session sess)
					throws HibernateException, SQLException {
				// TODO Auto-generated method stub
				
				Query query = sess.createSQLQuery(qStr.toString());
				query.setString(0, stkCode);
				query.setDate(0, refDate);
				int result = query.executeUpdate();
				return new Integer(result);
			}
			
		};
		
		getTemplate().execute(action);
		
		logger.info("update stock daily line data exrights");		
	}

	@Override
	public void updateStockExRights(final String stkCode) throws DataAccessException {

		final StringBuilder qStr = new StringBuilder("update ");
		qStr.append("STOCK_DAILY_LINE as mas_line ");
		qStr.append("left join ").append("STOCK_DAILY_LINE as sub_line ");
		qStr.append("on mas_line.stock_code = sub_line.stock_code ");
		qStr.append("and ");
		qStr.append("(")
			.append("mas_line.stock_date = adddate(sub_line.stock_date,1) ")
			.append("or (dayOfweek(mas_line.stock_date) = 2 and mas_line.stock_date = adddate(sub_line.stock_date,3)) ) ");
		qStr.append("set mas_line.ex_rights = ")
			.append("abs(mas_line.yield_rate * 100)>=11 && abs(round( (mas_line.open - sub_line.close)/sub_line.close * 100 , 3)) >= 11 ");
		qStr.append("where mas_line.stock_code = ? and sub_line.id is not null ");
		
		
		HibernateCallback action = new HibernateCallback() {
			@Override
			public Object doInHibernate(Session sess)
					throws HibernateException, SQLException {
				// TODO Auto-generated method stub
				
				Query query = sess.createSQLQuery(qStr.toString());
				query.setString(0, stkCode);
				int result = query.executeUpdate();
				return new Integer(result);
			}
			
		};
		
		getTemplate().execute(action);
		
		logger.info("update stock daily line data exrights");			
		
	}

	
	
	
	@Override
	public List<StockDailyKLine> findOverRatioLineByInCycle(Date startDate,
			Date endDate , double ratio ) throws DataAccessException {
		
		StringBuilder query = new StringBuilder("SELECT ");
		query.append("StockDailyKLine ");
		query.append("from ").append(StockDailyKLine.class.getName());
		query.append(" as StockDailyKLine ");
		query.append("WHERE ");
		query.append("StockDailyKLine.date >= ? ");		
		query.append("AND StockDailyKLine.date <= ? ");
		query.append("AND StockDailyKLine.yieldRate > ? ");
		query.append("ORDER BY StockDailyKLine.date DESC ");
		
		
		Collection paramColl = new Vector();
		paramColl.add( ExtensionFunction.changeFirstTimeOfDate( startDate ) );
		paramColl.add( ExtensionFunction.changeLastTimeOfDate( endDate ) );
		paramColl.add( ratio );
		if (logger.isDebugEnabled()) {
			logger.debug("Execute SQL :"+query.toString());
		}

		HibernateTemplate template = this.getTemplate();

		Iterator<StockDailyKLine> dataIter = template.iterate(query.toString() ,paramColl.toArray() );		
		StockDailyKLine entity = null;
		
		List<StockDailyKLine> resultList = new ArrayList<StockDailyKLine>(); 
		
		while (dataIter.hasNext()) {
			entity = (StockDailyKLine)dataIter.next();
			resultList.add( entity );		
		}
		
		return resultList;
	}

	
	
	

	
	
	@Override
	public void afterPropertiesSet() throws Exception {
		// TODO Auto-generated method stub
		
	}
		
}
